﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using rock.UI.common;

namespace rock.DAL
{
	/// <summary>
	/// 数据访问类:DalRENT_CUSTOM
	/// </summary>
	public partial class DalRENT_CUSTOM
	{
		public DalRENT_CUSTOM()
		{}
		#region  Method

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("SEQID", "RENT_CUSTOM"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int SEQID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from RENT_CUSTOM");
			strSql.Append(" where SEQID=@SEQID");
			SqlParameter[] parameters = {
					new SqlParameter("@SEQID", SqlDbType.Int,4)
			};
			parameters[0].Value = SEQID;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(rock.Model.ModelRENT_CUSTOM model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into RENT_CUSTOM(");
			strSql.Append("NAME,PHONE,ADDRESS,LINK_MAN,CARDID,TYPE,LOWER_MAN,BUSINESS_LICENSE,BRAND,WHOINROOM,NETE)");
			strSql.Append(" values (");
			strSql.Append("@NAME,@PHONE,@ADDRESS,@LINK_MAN,@CARDID,@TYPE,@LOWER_MAN,@BUSINESS_LICENSE,@BRAND,@WHOINROOM,@NETE)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@NAME", SqlDbType.VarChar,30),
					new SqlParameter("@PHONE", SqlDbType.VarChar,100),
					new SqlParameter("@ADDRESS", SqlDbType.VarChar,200),
					new SqlParameter("@LINK_MAN", SqlDbType.VarChar,100),
					new SqlParameter("@CARDID", SqlDbType.VarChar,20),
					new SqlParameter("@TYPE", SqlDbType.Int,4),
					new SqlParameter("@LOWER_MAN", SqlDbType.VarChar,30),
					new SqlParameter("@BUSINESS_LICENSE", SqlDbType.VarChar,200),
					new SqlParameter("@BRAND", SqlDbType.VarChar,100),
					new SqlParameter("@WHOINROOM", SqlDbType.VarChar,1000),
					new SqlParameter("@NETE", SqlDbType.VarChar,300)};
			parameters[0].Value = model.NAME;
			parameters[1].Value = model.PHONE;
			parameters[2].Value = model.ADDRESS;
			parameters[3].Value = model.LINK_MAN;
			parameters[4].Value = model.CARDID;
			parameters[5].Value = model.TYPE;
			parameters[6].Value = model.LOWER_MAN;
			parameters[7].Value = model.BUSINESS_LICENSE;
			parameters[8].Value = model.BRAND;
			parameters[9].Value = model.WHOINROOM;
			parameters[10].Value = model.NETE;

			object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(rock.Model.ModelRENT_CUSTOM model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update RENT_CUSTOM set ");
			strSql.Append("NAME=@NAME,");
			strSql.Append("PHONE=@PHONE,");
			strSql.Append("ADDRESS=@ADDRESS,");
			strSql.Append("LINK_MAN=@LINK_MAN,");
			strSql.Append("CARDID=@CARDID,");
			strSql.Append("TYPE=@TYPE,");
			strSql.Append("LOWER_MAN=@LOWER_MAN,");
			strSql.Append("BUSINESS_LICENSE=@BUSINESS_LICENSE,");
			strSql.Append("BRAND=@BRAND,");
			strSql.Append("WHOINROOM=@WHOINROOM,");
			strSql.Append("NETE=@NETE");
			strSql.Append(" where SEQID=@SEQID");
			SqlParameter[] parameters = {
					new SqlParameter("@NAME", SqlDbType.VarChar,30),
					new SqlParameter("@PHONE", SqlDbType.VarChar,100),
					new SqlParameter("@ADDRESS", SqlDbType.VarChar,200),
					new SqlParameter("@LINK_MAN", SqlDbType.VarChar,100),
					new SqlParameter("@CARDID", SqlDbType.VarChar,20),
					new SqlParameter("@TYPE", SqlDbType.Int,4),
					new SqlParameter("@LOWER_MAN", SqlDbType.VarChar,30),
					new SqlParameter("@BUSINESS_LICENSE", SqlDbType.VarChar,200),
					new SqlParameter("@BRAND", SqlDbType.VarChar,100),
					new SqlParameter("@WHOINROOM", SqlDbType.VarChar,1000),
					new SqlParameter("@NETE", SqlDbType.VarChar,300),
					new SqlParameter("@SEQID", SqlDbType.Int,4)};
			parameters[0].Value = model.NAME;
			parameters[1].Value = model.PHONE;
			parameters[2].Value = model.ADDRESS;
			parameters[3].Value = model.LINK_MAN;
			parameters[4].Value = model.CARDID;
			parameters[5].Value = model.TYPE;
			parameters[6].Value = model.LOWER_MAN;
			parameters[7].Value = model.BUSINESS_LICENSE;
			parameters[8].Value = model.BRAND;
			parameters[9].Value = model.WHOINROOM;
			parameters[10].Value = model.NETE;
			parameters[11].Value = model.SEQID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
        public SqlParamInfo Delete(int SEQID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from RENT_CUSTOM ");
			strSql.Append(" where SEQID=@SEQID");
			SqlParameter[] parameters = {
					new SqlParameter("@SEQID", SqlDbType.Int,4)
			};
			parameters[0].Value = SEQID;

            return new SqlParamInfo() { sql = strSql.ToString(), parameters = parameters };
            //int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
            //if (rows > 0)
            //{
            //    return true;
            //}
            //else
            //{
            //    return false;
            //}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string SEQIDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from RENT_CUSTOM ");
			strSql.Append(" where SEQID in ("+SEQIDlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


        

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public rock.Model.ModelRENT_CUSTOM GetModel(int SEQID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 SEQID,NAME,PHONE,ADDRESS,LINK_MAN,CARDID,TYPE,LOWER_MAN,BUSINESS_LICENSE,BRAND,WHOINROOM,NETE from RENT_CUSTOM ");
			strSql.Append(" where SEQID=@SEQID");
			SqlParameter[] parameters = {
					new SqlParameter("@SEQID", SqlDbType.Int,4)
			};
			parameters[0].Value = SEQID;

			rock.Model.ModelRENT_CUSTOM model=new rock.Model.ModelRENT_CUSTOM();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["SEQID"]!=null && ds.Tables[0].Rows[0]["SEQID"].ToString()!="")
				{
					model.SEQID=int.Parse(ds.Tables[0].Rows[0]["SEQID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["NAME"]!=null && ds.Tables[0].Rows[0]["NAME"].ToString()!="")
				{
					model.NAME=ds.Tables[0].Rows[0]["NAME"].ToString();
				}
				if(ds.Tables[0].Rows[0]["PHONE"]!=null && ds.Tables[0].Rows[0]["PHONE"].ToString()!="")
				{
					model.PHONE=ds.Tables[0].Rows[0]["PHONE"].ToString();
				}
				if(ds.Tables[0].Rows[0]["ADDRESS"]!=null && ds.Tables[0].Rows[0]["ADDRESS"].ToString()!="")
				{
					model.ADDRESS=ds.Tables[0].Rows[0]["ADDRESS"].ToString();
				}
				if(ds.Tables[0].Rows[0]["LINK_MAN"]!=null && ds.Tables[0].Rows[0]["LINK_MAN"].ToString()!="")
				{
					model.LINK_MAN=ds.Tables[0].Rows[0]["LINK_MAN"].ToString();
				}
				if(ds.Tables[0].Rows[0]["CARDID"]!=null && ds.Tables[0].Rows[0]["CARDID"].ToString()!="")
				{
					model.CARDID=ds.Tables[0].Rows[0]["CARDID"].ToString();
				}
				if(ds.Tables[0].Rows[0]["TYPE"]!=null && ds.Tables[0].Rows[0]["TYPE"].ToString()!="")
				{
					model.TYPE=int.Parse(ds.Tables[0].Rows[0]["TYPE"].ToString());
				}
				if(ds.Tables[0].Rows[0]["LOWER_MAN"]!=null && ds.Tables[0].Rows[0]["LOWER_MAN"].ToString()!="")
				{
					model.LOWER_MAN=ds.Tables[0].Rows[0]["LOWER_MAN"].ToString();
				}
				if(ds.Tables[0].Rows[0]["BUSINESS_LICENSE"]!=null && ds.Tables[0].Rows[0]["BUSINESS_LICENSE"].ToString()!="")
				{
					model.BUSINESS_LICENSE=ds.Tables[0].Rows[0]["BUSINESS_LICENSE"].ToString();
				}
				if(ds.Tables[0].Rows[0]["BRAND"]!=null && ds.Tables[0].Rows[0]["BRAND"].ToString()!="")
				{
					model.BRAND=ds.Tables[0].Rows[0]["BRAND"].ToString();
				}
				if(ds.Tables[0].Rows[0]["WHOINROOM"]!=null && ds.Tables[0].Rows[0]["WHOINROOM"].ToString()!="")
				{
					model.WHOINROOM=ds.Tables[0].Rows[0]["WHOINROOM"].ToString();
				}
				if(ds.Tables[0].Rows[0]["NETE"]!=null && ds.Tables[0].Rows[0]["NETE"].ToString()!="")
				{
					model.NETE=ds.Tables[0].Rows[0]["NETE"].ToString();
				}
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select SEQID,NAME,PHONE,ADDRESS,LINK_MAN,CARDID,TYPE,LOWER_MAN,BUSINESS_LICENSE,BRAND,WHOINROOM,NETE ");
			strSql.Append(" FROM RENT_CUSTOM ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" SEQID,NAME,PHONE,ADDRESS,LINK_MAN,CARDID,TYPE,LOWER_MAN,BUSINESS_LICENSE,BRAND,WHOINROOM,NETE ");
			strSql.Append(" FROM RENT_CUSTOM ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		 
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public PageDataSet GetListByPage( String where, string orderby, int pageNo, int pageSize)
		{
//             string sql = @"SELECT SEQID,NAME,PHONE,ADDRESS,LINK_MAN,CARDID,TYPE,LOWER_MAN,BUSINESS_LICENSE,BRAND,WHOINROOM,NETE , 
//                            (SELECT COUNT(*) FROM RENT_CONTRACT WHERE RENT_CONTRACT.RENT_CUSTOM_SEQID = RENT_CUSTOM.SEQID AND RENT_CONTRACT.STATUS = 1  )  AS ROOM_COUNT
//                            FROM RENT_CUSTOM ";



            String sql = @"SELECT  RENT_CUSTOM.SEQID,NAME,PHONE,ADDRESS,LINK_MAN,CARDID,TYPE,LOWER_MAN,BUSINESS_LICENSE,BRAND,WHOINROOM,NETE , 
	        (SELECT COUNT(*) FROM RENT_CONTRACT WHERE RENT_CONTRACT.RENT_CUSTOM_SEQID = RENT_CUSTOM.SEQID AND RENT_CONTRACT.STATUS = 1  )  AS ROOM_COUNT
            FROM RENT_CUSTOM
	        LEFT JOIN RENT_CONTRACT ON RENT_CUSTOM.SEQID = RENT_CONTRACT.RENT_CUSTOM_SEQID AND RENT_CONTRACT.STATUS = 1
	        LEFT JOIN HOUSE_ROOM ON RENT_CONTRACT.HOUSE_ROOM_SEQID = HOUSE_ROOM.SEQID AND HOUSE_ROOM.STATUS = 1
	        LEFT JOIN  
	        (
	        SELECT  HOUSE_XIAOQU.NAME as XIAOQU_NAME , HOUSE_DONG.NAME AS DONG_NAME, ALL_FLOOR_START,ALL_FLOOR_END , HAS_DIANTI ,
	        HOUSE_XIAOQU.SEQID  as XIAOQU_SEQID , HOUSE_DONG.SEQID  as DONG_SEQID 
	        FROM HOUSE_XIAOQU , HOUSE_DONG WHERE HOUSE_XIAOQU.SEQID = HOUSE_DONG.HOUSE_XIAOQU_SEQID
	        ) AS TEMP ON HOUSE_ROOM.HOUSE_DONG_SEQID = TEMP.DONG_SEQID 	
	        ";
	 
            String group = @"GROUP BY RENT_CUSTOM.SEQID,NAME,PHONE,ADDRESS,LINK_MAN,CARDID,TYPE,LOWER_MAN,BUSINESS_LICENSE,BRAND,WHOINROOM,NETE ";

            if (where != null && !where.Equals(""))
            {
                 sql += " WHERE " + where;
            }

             sql += group;
             PageDataSet pds = new PageDataSet();
             pds.AllRecord = DbHelperSQL.GetRecordCount(sql);
             pds.DataSet = DbHelperSQL.GetListByPage(sql, orderby, pageNo, pageSize);

             return pds;
        }

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "RENT_CUSTOM";
			parameters[1].Value = "SEQID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  Method
	}
}

